﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_device_dml_upsert]
	@TVP as flcr.DEVICE_UDT READONLY
AS	

BEGIN TRY
  
	MERGE	[flcr].[DEVICE] A
	USING	(SELECT	[DEVICE_ID]
				,[AUDIT_USER]
				,[AUDIT_DTTM]
				,[AUDIT_DTTM_TZ]	
				,[AUDIT_MOD_USER]
				,[AUDIT_MOD_DTTM]
				,[AUDIT_MOD_DTTM_TZ]
				,[RLS_MASK]
				,[RLS_OWNER]
				,[WRKFL_ITEM_ID]
				,[WRKFL_STATUS_ID]
				,[ACTIVE_FLG]
				,[DEVICE_ASSET_NAME]
				,[DEVICE_SERIAL_NUM]
				,[DEVICE_ASSET_TAG]
				,[PARENT_ID]
				,[DEVICE_TYPE]
				,[DEVICE_PLATFORM_ID]
				,[DOMAIN_ID]
				,[SLA_ID]
				,[FACILITY_ID]
			   FROM	@TVP) B
	    ON	A.[DEVICE_ID] = B.[DEVICE_ID]
	  WHEN MATCHED THEN
			UPDATE SET 
				A.[AUDIT_MOD_USER] = B.[AUDIT_MOD_USER]
				,A.[AUDIT_MOD_DTTM] = B.[AUDIT_MOD_DTTM]
				,A.[AUDIT_MOD_DTTM_TZ] = B.[AUDIT_MOD_DTTM_TZ]
				,A.[RLS_MASK] = B.[RLS_MASK]
				,A.[RLS_OWNER] = B.[RLS_OWNER]
				,A.[WRKFL_ITEM_ID] = B.[WRKFL_ITEM_ID]
				,A.[WRKFL_STATUS_ID] = B.[WRKFL_STATUS_ID]
				,A.[ACTIVE_FLG] = B.[ACTIVE_FLG]
				,A.[DEVICE_ASSET_NAME] = B.[DEVICE_ASSET_NAME]
				,A.[DEVICE_SERIAL_NUM] = B.[DEVICE_SERIAL_NUM]
				,A.[DEVICE_ASSET_TAG] = B.[DEVICE_ASSET_TAG]
				,A.[PARENT_ID] = B.[PARENT_ID]
				,A.[DEVICE_TYPE] = B.[DEVICE_TYPE]
				,A.[DEVICE_PLATFORM_ID] = B.[DEVICE_PLATFORM_ID]
				,A.[DOMAIN_ID] = B.[DOMAIN_ID]
				,A.[SLA_ID] = B.[SLA_ID]
				,A.[FACILITY_ID] = B.[FACILITY_ID]
	  WHEN NOT MATCHED THEN	  	  
		INSERT ([AUDIT_USER]
					,[AUDIT_DTTM]
					,[AUDIT_DTTM_TZ]
					,[AUDIT_MOD_USER]
					,[AUDIT_MOD_DTTM]
					,[AUDIT_MOD_DTTM_TZ]
					,[RLS_MASK]
					,[RLS_OWNER]
					,[WRKFL_ITEM_ID]
					,[WRKFL_STATUS_ID]
					,[ACTIVE_FLG]
					,[DEVICE_ASSET_NAME]
					,[DEVICE_SERIAL_NUM]
					,[DEVICE_ASSET_TAG]
					,[PARENT_ID]
					,[DEVICE_TYPE]
					,[DEVICE_PLATFORM_ID]
					,[DOMAIN_ID]
					,[SLA_ID]
					,[FACILITY_ID])
		VALUES (B.[AUDIT_USER]
					,B.[AUDIT_DTTM]
					,B.[AUDIT_DTTM_TZ]
					,B.[AUDIT_MOD_USER]
					,B.[AUDIT_MOD_DTTM]
					,B.[AUDIT_MOD_DTTM_TZ]
					,B.[RLS_MASK]
					,B.[RLS_OWNER]
					,B.[WRKFL_ITEM_ID]
					,B.[WRKFL_STATUS_ID]
					,B.[ACTIVE_FLG]
					,B.[DEVICE_ASSET_NAME]
					,B.[DEVICE_SERIAL_NUM]
					,B.[DEVICE_ASSET_TAG]
					,B.[PARENT_ID]
					,B.[DEVICE_TYPE]
					,B.[DEVICE_PLATFORM_ID]
					,B.[DOMAIN_ID]
					,B.[SLA_ID]
					,B.[FACILITY_ID])
		OUTPUT inserted.*;	
				
END TRY
BEGIN CATCH
	EXEC [flcr].[utl_GetDBError];
END CATCH